package matlap.database.jdbc;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.TaskDAO;
import matlap.shared.Task;

public class TaskJdbcDAO implements TaskDAO {

	Connection con = null;

	/*
	 * Inserts task into tasks table by adding Task.
	 */

	public boolean insertTask(Task task) {

		boolean ok = false;
		Calendar currentDate = Calendar.getInstance();
		SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
		String dateNow = formatter.format(currentDate.getTime());

		String s = new String("INSERT INTO tasks VALUES (?,?,?,?,?,?)");
		FileInputStream fileInputStream = null;
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			File file = new File(task.getTaskText());
			fileInputStream = new FileInputStream(task.getTaskText());
			PreparedStatement ps = con.prepareStatement(s);

			ps.setString(1, task.getUserName());
			ps.setInt(2, task.getVolume());

			ps.setBinaryStream(3, fileInputStream, file.length());
			ps.setString(4, dateNow);
			ps.setInt(5, 0);
			ps.setInt(6,task.getType());
			System.out.println(task.getType());
			ps.executeUpdate();
			ok = true;
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return ok;

	}

	/**
	 * Turns back Tasks FROM the task table table in Task list by adding
	 * username.
	 */

	@Override
	public List<Task> getTaskByUserName(String userName) {
		List<Task> list = new ArrayList<Task>();
		InputStream in = null;
		String query = "SELECT task_text, tasks.volume, Received, Corected,deadline, Type FROM tasks,deadlines WHERE username='"
				+ userName + "' and tasks.volume=deadlines.volume";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				Task task = new Task();
				task.setUserName(userName);
				task.setVolume(Integer.parseInt(rs.getString("volume")));
				task.setType(Integer.parseInt(rs.getString("Type")));
				String task_title = rs.getString("task_text");
				int hossz = task_title.length();
				byte[] b = new byte[hossz];
				in = rs.getBinaryStream("task_text");
				try {
					in.read(b, 0, hossz);
					System.out.println("task.getType(): "+task.getType());
					if (task.getType() == 0){ 
						OutputStream out = new FileOutputStream("pdf" + i + ".pdf");
						task_title = "pdf" + i + ".pdf";
						out.write(b, 0, hossz);
						out.close();

					}
					if (task.getType() == 1){
						OutputStream out = new FileOutputStream("jpg" + i + ".jpg");
						task_title = "jpg" + i + ".jpg";
						out.write(b, 0, hossz);
						out.close();
					}			
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}

				task.setTaskText(task_title);

				task.setReceived(rs.getString("Received"));
				task.setCorrected(Integer.parseInt(rs.getString("Corected")));
				task.setDeadline(rs.getString("deadline"));
				list.add(i, task);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;

	}

	public List<Task> getTasks() {
		List<Task> list = new ArrayList<Task>();
		InputStream in = null;
		String query = "SELECT people.username,first_name, last_name,task_text, tasks.volume, Received, Corected,deadline, Type "
				+ "FROM tasks, deadlines, people"
				+ " WHERE tasks.username=people.username and tasks.volume=deadlines.volume ORDER BY tasks.volume, last_name, first_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				Task task = new Task();
				task.setUserName(rs.getString("userName"));
				task.setVolume(Integer.parseInt(rs.getString("volume")));
				task.setType(rs.getInt("Type"));
				String task_title = rs.getString("task_text");
				int hossz = task_title.length();
				byte[] b = new byte[hossz];
				in = rs.getBinaryStream("task_text");				
				try {
					in.read(b, 0, hossz);
					System.out.println("task.getType(): "+task.getType());
					if (task.getType() == 0){ 
						OutputStream out = new FileOutputStream("pdf" + i + ".pdf");
						task_title = "pdf" + i + ".pdf";
						out.write(b, 0, hossz);
						out.close();

					}
					if (task.getType() == 1){
						OutputStream out = new FileOutputStream("jpg" + i + ".jpg");
						task_title = "jpg" + i + ".jpg";
						out.write(b, 0, hossz);
						out.close();
					}			
				} catch (FileNotFoundException e) {
					e.printStackTrace();
				} catch (IOException e) {
					e.printStackTrace();
				}

				task.setTaskText(task_title);

				task.setReceived(rs.getString("Received"));
				task.setCorrected(Integer.parseInt(rs.getString("Corected")));
				task.setDeadline(rs.getString("deadline"));
				String nev = rs.getString("last_name") + " "
						+ rs.getString("first_name");
				task.setName(nev);
				list.add(i, task);
				i++;

			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;

	}

	@Override
	public int getTasksNumber() {
		int nr = 0;
		String query = "SELECT COUNT(username) AS darab FROM tasks";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			if (rs.next())
				nr = rs.getInt("darab");

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return nr;
	}

}
